package org.example;

import lombok.Getter;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Getter

public class ExcelReader {
    private static final Log log = LogFactory.getLog(ExcelReader.class);

    private List<String> excelHeaders = new ArrayList<>();

    public List<Map<String, Object>> readExcel(File file) throws IOException {
        log.info("开始读取Excel文件：" + file.getAbsolutePath());
        
        List<Map<String, Object>> dataList = new ArrayList<>();
        
        try (FileInputStream fis = new FileInputStream(file);
             Workbook workbook = WorkbookFactory.create(fis)) {
            
            for (Sheet sheet : workbook) {
                Row headerRow = sheet.getRow(0);
                List<String> headers = new ArrayList<>();
                for (Cell cell : headerRow) {
                    String header = cell.getStringCellValue();
                    headers.add(header);
                    excelHeaders.add(header);
                }
                
                for (int i = 1; i <= 100; i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) continue;
                    
                    Map<String, Object> rowData = new HashMap<>();
                    for (int j = 0; j < headers.size(); j++) {
                        Cell cell = row.getCell(j, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                        rowData.put(headers.get(j), getCellValue(cell));
                    }
                    dataList.add(rowData);
                }
            }
            
            log.info("Excel文件读取完成，共读取" + dataList.size() + "条记录");
            return dataList;
        } catch (Exception e) {
            log.error("读取Excel文件失败：" + file.getAbsolutePath(), e);
            throw new IOException("读取Excel文件失败: " + e.getMessage(), e);
        }
    }
    
    public List<Map<String, Object>> readExcel(File file, String sheetName) throws IOException {
        log.info("开始读取Excel文件：" + file.getAbsolutePath() + ", 指定工作表：" + sheetName);
        
        List<Map<String, Object>> allData = new ArrayList<>();
        
        try (FileInputStream fis = new FileInputStream(file);
             Workbook workbook = WorkbookFactory.create(fis)) {
            
            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                throw new IllegalArgumentException("Sheet '" + sheetName + "' not found in workbook");
            }
    
            Row headerRow = sheet.getRow(0);
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                String header = cell.getStringCellValue();
                headers.add(header);
                excelHeaders.add(header);
            }
    
            for (int i = 1; i <= sheet.getLastRowNum()-1; i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;
    
                Map<String, Object> rowData = new HashMap<>();
                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = row.getCell(j, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    rowData.put(headers.get(j), getCellValue(cell));
                }
                allData.add(rowData);
            }
            
            log.info("Excel文件读取完成，工作表：" + sheetName + ", 共读取" + allData.size() + "条记录");
            return allData;
        } catch (Exception e) {
            log.error("读取Excel文件失败：" + file.getAbsolutePath() + ", 工作表：" + sheetName, e);
            throw new IOException("读取Excel文件失败: " + e.getMessage(), e);
        }
    }

    private Object getCellValue(Cell cell) {
        if (cell == null) return null;
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
                } else {
                    double value = cell.getNumericCellValue();
                    if (value == (long) value) return (long) value;
                    else return value;
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return null;
            default:
                return null;
        }
    }
}